import XLSX from 'xlsx';
import path from 'path';
import { fileURLToPath } from 'url';
import fs from 'fs';
import manHelper from './manHelper.js';

class ExcelHelper {
    async readDeliveryFile() {
        try {
            const __dirname = path.dirname(fileURLToPath(import.meta.url));
            const filePath = path.join(__dirname, '../../交付.xlsx');

            if (!fs.existsSync(filePath)) {
                throw new Error(`Excel文件不存在于: ${filePath}`);
            }

            const workbook = XLSX.readFile(filePath);

            let sheet = workbook.Sheets['讲师交付情况明细'];
            return sheet;
        } catch (error) {
            console.error('读取Excel文件失败:', error);
            throw error;
        }
    }

    /**
     * 从Excel文件中读取打卡数据，并返回一个Map，key为每个人对应的token，value为punchDataItem[]
     * @returns punchDataMap - <token, punchDataItem[]>
     */
    async getPunchData() {
        let sheet = await this.readDeliveryFile();

        let data = XLSX.utils.sheet_to_json(sheet);
        const punchDataMap = new Map();

        console.info(data)
        for (let i = 0; i < data.length; i++) {
            console.info(data[i]['讲师姓名'])
            const name = data[i]['讲师姓名'];
            const token = manHelper.getTokenByName(name);
            if (!token) {
                console.error(`未找到姓名为 ${name} 的Man对象`);
                continue; 
            }
            const startPunchDate = data[i]['上课时间'];
            const photoPunchDate = data[i]['拍照时间'];
            const endPunchDate = data[i]['下课时间'];
            const dataList = getDateRange(data[i]['开班日期'], data[i]['结班日期']);
            console.info(dataList);
            const punchList = [];
            if (punchDataMap.has(token)) {
                const tmp = punchDataMap.get(token);
                punchList.push(...tmp);
            }
            for (let j = 0; j < dataList.length; j++) {
                const punchItem = {
                    date: dataList[j],
                    start_punch_date: dataList[j] + ' ' + startPunchDate,
                    photo_punch_date: dataList[j] + ' ' + photoPunchDate,
                    end_punch_date: dataList[j] + ' ' + endPunchDate
                };
                punchList.push(punchItem);
            }

            punchDataMap.set(token, punchList);
        }
        console.info(punchDataMap);
        return punchDataMap;
    }
}

function encodeCell(r, c) {
    return XLSX.utils.encode_cell({ r, c });
}

function deleteRow(ws, index) {
    const range = XLSX.utils.decode_range(ws['!ref']);

    for (let row = index; row < range.e.r; row++) {
        for (let col = range.s.c; col <= range.e.c; col++) {
            ws[encodeCell(row, col)] = ws[encodeCell(row + 1, col)];
        }
    }

    range.e.r--;

    ws['!ref'] = XLSX.utils.encode_range(range.s, range.e);
}

function deleteCol(ws, index) {
    const range = XLSX.utils.decode_range(ws['!ref']);

    for (let col = index; col < range.e.c; col++) {
        for (let row = range.s.r; row <= range.e.r; row++) {
            ws[encodeCell(row, col)] = ws[encodeCell(row, col + 1)];
        }
    }

    range.e.c--;

    ws['!ref'] = XLSX.utils.encode_range(range.s, range.e);
}

/**
 * 根据开始日期和结束日期获取这个日期范围内的所有日期
 * @param {string|number} startDateStr - 开始日期，格式为YYYY-MM-DD，例如2024-01-01
 * @param {string|number} endDateStr - 结束日期，格式为YYYY-MM-DD，例如2024-01-31
 * @returns {string[]} - 返回日期范围内的所有日期数组，每个日期格式为YYYY-MM-DD
 * @throws {Error} - 如果日期格式不正确会抛出错误
 * @example
 * getDateRange('2024-01-01', '2024-01-03')
 * // 返回 ['2024-01-01', '2024-01-02', '2024-01-03']
 */
function getDateRange(startDateStr, endDateStr) {
    // 确保输入是字符串
    startDateStr = String(startDateStr);
    endDateStr = String(endDateStr);

    // 检查日期格式是否正确（YYYY-MM-DD）
    if (!/^\d{4}-\d{2}-\d{2}$/.test(startDateStr) || !/^\d{4}-\d{2}-\d{2}$/.test(endDateStr)) {
        throw new Error('日期格式应为YYYY-MM-DD');
    }
    
    const startDate = new Date(startDateStr);
    const endDate = new Date(endDateStr);

    const dateArray = [];
    let currentDate = new Date(startDate);

    while (currentDate <= endDate) {
        const year = currentDate.getFullYear();
        const month = String(currentDate.getMonth() + 1).padStart(2, '0');
        const day = String(currentDate.getDate()).padStart(2, '0');
        dateArray.push(`${year}-${month}-${day}`);

        currentDate.setDate(currentDate.getDate() + 1);
    }

    return dateArray;
}

let excelHelper = new ExcelHelper();
export default excelHelper;

// 测试代码
// excelHelper.getPunchData();